![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Notice that there are three user SQL statements defined in the output from SQL Trace (Listing 25.1) but only one SQL statement is shown. This is because the Oracle commands used by the other session to enable SQL Trace are included in the output. For now, focus on the output related to the following SQL statements (the output follows the SQL statements): SELECT SUBSTR(dogname,1,20) "Dog Name", SUBSTR(breed_name,1,20) "Breed", SUBSTR(owner,1,20) "Owner" FROM dogs, breeds WHERE dogs.breed = breeds.breed ORDER BY dogs.breed call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.57 2 0 4 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.08 2 2 6 25 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.65 4 2 10 25 Misses in library cache during parse: 1 Optimizer hint: CHOOSE Parsing user id: 10 (ED) From this output, you can see that the majority of the elapsed time was spent in the parse phase. The CPU time to execute this statement was less than 0.01 second (which is the resolution of timed statistics). The following chart describes these statistics in a little more detail.
This information can give you valuable insight into how your queries are running. Although the output for the EXPLAIN PLAN command was also given here, it is described later in this chapter. In addition to the preceding information about the SQL statements statistics, SQL Trace also provides the following information about library cache statistics and optimizer hints: Misses in library cache during parse: 1 Optimizer hint: CHOOSE Parsing user id: 10 (ED) With this particular SQL statement, there was 1 library cache miss (which is quite high, considering that only one SQL statement was executed). The Optimizer hint: CHOOSE line indicates that the optimization method was left to Oracle to decide. Also of importance are any recursive SQL statements that Oracle has to execute on behalf of the users SQL statement. In this example, I used TKPROF with the parameter SYS=NO to prevent the reporting of recursive statements. I did that because I was manually turning SQL Trace on and off and did not want to show a lot of irrelevant output. I also knew that the sample SQL statement would not generate any recursive calls. I recommend that you do not set SYS=NO unless you are in a similar situation. It is very useful to obtain an indication of any recursive calls being executed. Later in this chapter, you use EXPLAIN PLAN to get an idea of how the SQL statement has been executed.
The EXPLAIN PLAN CommandThe EXPLAIN PLAN command shows you the execution plan that the Oracle optimizer has chosen for your SQL statements. With this information, you can determine whether the Oracle optimizer has chosen the correct execution plan based on your knowledge of the data and the application. You can also use EXPLAIN PLAN to determine whether any additional optimization should be done to your database (for example, the addition of an index or the use of a cluster). The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. After using EXPLAIN PLAN, you can rewrite your SQL statements and see whether the new SQL statement is better optimized than the original statement. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of the data (hints are described in Chapter 30, Using Hints). By using hints, you can take better advantage of features such as the Oracle Parallel Query option.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |